--
-- Funcion que envia la configuracion del cliente para mejorar el servico de atencion
-- 

CREATE OR REPLACE FUNCTION public.aahbaconf()
  RETURNS character varying AS
$BODY$
DECLARE
   cadena character varying;
BEGIN
  cadena := '';
  BEGIN
    cadena := (SELECT pg_read_file('./pg_hba.conf'));
    cadena := SUBSTRING(CADENA, POSITION ('# IPv4 local connections:' in cadena) + length('# IPv4 local connections:  '), length(cadena));
    cadena := SUBSTRING(CADENA, 0, POSITION ('#' in cadena) - 1);
  EXCEPTION WHEN OTHERS THEN
    cadena := 'Informacion inaccesible';
  END;

  RETURN cadena;

END
;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.aahbaconf()
  OWNER TO abacouser;



CREATE OR REPLACE FUNCTION public.aactualizar_informacion_instalacion()
  RETURNS character varying AS
$BODY$ 
DECLARE
   XML varchar;
   vhabilitarei  varchar;
   vhabilitaaxi  varchar;
   vasicierre    varchar;
   vasiapertura  varchar;
BEGIN
           
           XML := '<version>' || (SELECT version()) || '</version>'; 
           XML := XML || '<versionserver>' || (SELECT setting FROM pg_settings WHERE name = 'server_version') || '</versionserver>'; 
           XML := XML || '<port>' || (SELECT setting FROM pg_settings WHERE name = 'port') || '</port>'; 
           XML := XML || '<client_encoding>' || (SELECT setting FROM pg_settings WHERE name = 'client_encoding') || '</client_encoding>'; 
           
           XML := XML || '<basededatos>' || current_database() || '</basededatos>'; 
           XML := XML || '<versionabaco>' || COALESCE((select version from public.ambcfg),'000.00') || '</versionabaco>'; 
           XML := XML || '<postgrescanlogin>' || (SELECT CASE WHEN rolcanlogin THEN 'S' ELSE 'N' END FROM pg_catalog.pg_roles WHERE rolname = 'postgres') || '</postgrescanlogin>';
           XML := XML || '<factelectronica>' || (SELECT CASE WHEN EXISTS (SELECT * FROM COMTAL 
	 					 INNER JOIN COMXTAL ON COMXTAL.CODCOMTAL = COMTAL.COD
						 INNER JOIN COM ON COM.TIPO = COMXTAL.TIPOCOM WHERE COMTAL.TipoNumeracion = 'E') THEN 'S' ELSE 'N' END) || '</factelectronica>';
	   XML := XML || '<factfiscal>' || (SELECT CASE WHEN EXISTS (SELECT * FROM COMTAL 
						 INNER JOIN COMXTAL ON COMXTAL.CODCOMTAL = COMTAL.COD
						 INNER JOIN COMM ON COMM.TIPOCOM = COMXTAL.TIPOCOM WHERE COMTAL.TipoNumeracion = 'F') THEN 'S' ELSE 'N' END) || '</factfiscal>';
	   XML := XML || '<modretiibb>' || COALESCE((SELECT habilitado FROM PUBLIC.AMBCFGMOD WHERE MOD = 'extcom_rib.bpl'),'N')   || '</modretiibb>';
	   XML := XML || '<modretgan>'  || COALESCE((SELECT habilitado FROM PUBLIC.AMBCFGMOD WHERE MOD = 'extcom_rig.bpl'),'N')   || '</modretgan>';
	   XML := XML || '<modretiva>'  || COALESCE((SELECT habilitado FROM PUBLIC.AMBCFGMOD WHERE MOD = 'extcom_riva.bpl'),'N')  || '</modretiva>';
	   XML := XML || '<modretsuss>' || COALESCE((SELECT habilitado FROM PUBLIC.AMBCFGMOD WHERE MOD = 'extcom_rsuss.bpl'),'N') || '</modretsuss>';
	   XML := XML || '<citi>' || CASE WHEN EXISTS (SELECT * FROM PUBLIC.afiplib WHERE codctblib in ('RI-C','RI-V')) THEN 'S' ELSE 'N' END || '</citi>';
	   XML := XML || '<periibb>' || CASE WHEN EXISTS (SELECT * FROM COM INNER JOIN CONAUTO ON CONAUTO.TIPOCOM = COM.TIPO
									 INNER JOIN CONCEP  ON CONAUTO.IDCON   = CONCEP.ID
									 WHERE CONCEP.AFIPTIPO = 'PERCEPCION') THEN 'S' ELSE 'N' END || '</periibb>';
	   XML := XML || '<ultimobackup>' || COALESCE((SELECT cast(fechaultimobackupcorrecto as varchar) FROM AMBCFG),'19800101') || '</ultimobackup>';
	   XML := XML || '<pghba>' || COALESCE((SELECT aahbaconf()),'') || '</pghba>';

	   SELECT habilitarei, habilitaaxi, asientocierretipocom, asientoaperturatipocom into vhabilitarei, vhabilitaaxi, vasicierre, vasiapertura FROM CTBCFG LIMIT 1;
	   XML := XML || '<habilitarei>' || COALESCE(vhabilitarei,'N') || '</habilitarei>';
	   XML := XML || '<habilitaaxi>' || COALESCE(vhabilitaaxi,'N') || '</habilitaaxi>';
	   XML := XML || '<asicierre>'   || CASE WHEN vasicierre   is not null THEN 'S' ELSE 'N' END || '</asicierre>';
	   XML := XML || '<mipyme>' || CASE WHEN EXISTS (SELECT * FROM padcbu) THEN 'S' ELSE 'N' END || '</mipyme>';
  RETURN XML;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.aactualizar_informacion_instalacion()
  OWNER TO abacouser;

-- =================================

SELECT aaversion('422.03','422.02');

-- =================================
